if exists (select 1 from sysobjects where name = 'chk_abteilungverplant' and type = 'P')
begin
   drop procedure chk_abteilungverplant
   print 'Procedure: chk_abteilungverplant deleted ...'
end
go
create procedure chk_abteilungverplant(
   @monatID        int  = 1,
   @schemaID       int  = 1,
   @jahrID         int  = 1
)
as
begin
  set nocount on
   
  select amount = sum(isnull(ps.AbteilungID,0)) from ArbeitsPlan ap 
    inner join PlanSchema ps
      on ps.SchemaID = ap.SchemaID
     and ps.AbteilungID in (select distinct 
                                   abteilungid = ps.AbteilungID
                              from PlanSchema ps 
                             where ps.SchemaID = @schemaID)
   where ap.ATID = @monatID
     and ap.JahrID = @jahrID

end
go
print 'Procedure: chk_abteilungverplant done ...'
go
grant exec on chk_abteilungverplant to prsadmins with grant option
go
grant exec on chk_abteilungverplant to prsusers
go

